package com.coalmine.api.util;

import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.*;


@Slf4j
public class GetMultiTableComment {

    /**
     * 查询表所有字段+备注
     *  conn不关闭，在调用getRDBMSColumnsPropertie()方法中关闭
     * @param conn
     * @param type
     * @param table
     * @return
     */
    public static List<JSONObject> getRDBMSColumnsPropertie(Connection conn, String type, String table) {
        String sql;
        switch (type) {
            case "postgresql":
                sql = "select * from \"" + table + "\" where 1=2";
                break;
            case "oracle":
                sql = "SELECT b.comments , a.column_name as name, a.data_type  as type " +
                        "FROM user_tab_columns a, user_col_comments b " +
                        "WHERE a.TABLE_NAME = '"+table+"' and b.table_name = '"+table+"' and a.column_name = b.column_name";
                break;
            case "sqlserver":
                sql = "SELECT t.[name] AS t_name,c.[name] AS name,tp.[name] AS type,cast(ep.[value] as varchar(100)) AS comment \n" +
                        "FROM sys.tables AS t INNER JOIN sys.columns \n" +
                        "AS c ON t.object_id = c.object_id LEFT JOIN sys.extended_properties AS  ep \n" +
                        "ON ep.major_id = c.object_id AND ep.minor_id = c.column_id  \n" +
                        "LEFT JOIN sys.types AS tp ON tp.user_type_id = c.user_type_id where t.name = '"+table+"'";
                break;
            case "clickhouse":
                sql = "desc " + table;
                break;
            default:
                sql = "select * from " + table + " where 1=2";
        }
        if(type.equals("oracle")){
            return getRDBMSColumnsPropertieOracle(conn, sql, table);
        }
        if( type.equals("clickhouse") || type.equals("sqlserver") ){
            return getRDBMSColumnsPropertieChAndSqlserver(conn, sql, table);
        }
        return getRDBMSColumnsPropertieCommon(conn, sql, table);
    }


    /**
     * 实际获取Oracle字段和字段描述
     * conn不关闭，在调用getRDBMSColumnsPropertie()方法中关闭
     * @param conn
     * @param sql
     * @param table
     * @return
     */
    public static List<JSONObject> getRDBMSColumnsPropertieOracle(Connection conn,  String sql, String table) {
        List<JSONObject> list = new ArrayList<>();
        PreparedStatement pst = null;
        try {
            log.info(sql);
            pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                String fieldTypeName= rs.getString("name");
                String TypeName= rs.getString("type");
                String fieldJavaTypeName= rs.getString("type");
                //无法指定comment别名
                String columnRemark= rs.getString("comments");
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("fieldTypeName", fieldTypeName);//数据库字段类型名
                jsonObject.put("fieldJavaTypeName", JdbcUtil.convertType(fieldJavaTypeName));//数据库字段类型 映射到java的类型名
                if (fieldTypeName.contains("."))
                    fieldTypeName = fieldTypeName.split("\\.")[1];
                jsonObject.put("label", fieldTypeName);//表字段名
                //表字段说明
                jsonObject.put("columnRemark", columnRemark);
                list.add(jsonObject);
            }
            return list;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        } finally {
            try {
                if (pst != null)
                    pst.close();
                //if (conn != null)
                    //conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 实际获取clickhouse和sqlserver字段和字段描述
     * conn不关闭，在调用getRDBMSColumnsPropertie()方法中关闭
     * @param conn
     * @param sql
     * @param table
     * @return
     */
    public static List<JSONObject> getRDBMSColumnsPropertieChAndSqlserver(Connection conn,  String sql, String table) {
        List<JSONObject> list = new ArrayList<>();
        PreparedStatement pst = null;
        try {
            log.info(sql);
            pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                String fieldTypeName= rs.getString("name");
                String TypeName= rs.getString("type");
                String fieldJavaTypeName= rs.getString("type");
                String columnRemark= rs.getString("comment");
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("fieldTypeName", fieldTypeName);//数据库字段类型名
                jsonObject.put("fieldJavaTypeName", JdbcUtil.convertType(fieldJavaTypeName));//数据库字段类型映射到java的类型名
                if (fieldTypeName.contains("."))
                    fieldTypeName = fieldTypeName.split("\\.")[1];
                jsonObject.put("label", fieldTypeName);//表字段
                //表字段说明
                jsonObject.put("columnRemark", columnRemark);
                list.add(jsonObject);
            }
            return list;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        } finally {
            try {
                if (pst != null)
                    pst.close();
                //if (conn != null)
                //    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 实际获取非clickhouse字段和字段描述
     * conn不关闭，在调用getRDBMSColumnsPropertie()方法中关闭
     * @param conn
     * @param sql
     * @param table
     * @return
     */
    public static List<JSONObject> getRDBMSColumnsPropertieCommon(Connection conn, String sql, String table) {
        List<JSONObject> list = new ArrayList<>();
        ResultSet rs = null;
        try {
            log.info(sql);
            DatabaseMetaData metaData = conn.getMetaData();
            rs=metaData.getColumns(conn.getCatalog(), "%", table, "%");
            while (rs.next()) {
                String fieldTypeName= rs.getString(ResultSetColumnKeys.COLUMN_NAME.name());
                String TypeName= rs.getString(ResultSetColumnKeys.TYPE_NAME.name());
                String fieldJavaTypeName= rs.getString(ResultSetColumnKeys.TYPE_NAME.name());
                String columnRemark= rs.getString(ResultSetColumnKeys.REMARKS.name());
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("fieldTypeName", fieldTypeName);//数据库字段类型名
                jsonObject.put("fieldJavaTypeName", JdbcUtil.convertType(fieldJavaTypeName));//数据库字段类型映射到java的类型名
                if (fieldTypeName.contains("."))
                    fieldTypeName = fieldTypeName.split("\\.")[1];
                jsonObject.put("label", fieldTypeName);//表字段
                //表字段说明
                jsonObject.put("columnRemark", columnRemark);
                list.add(jsonObject);
            }
            return list;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        } finally {
            try {
                if (rs != null)
                    rs.close();
                //if (conn != null)
                //    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }




}
